<?php
class U
{
    static function get_setoran_kas_per_sales($tgl, $sales)
    {
        $setor = array();
        $setor['faktur'] = (int)Yii::app()->db->createCommand("SELECT IFNULL(SUM(pp.uang_muka),0) FROM {{penjualan}} pp
            WHERE pp.tgl = :tgl AND pp.salesman_id = :sales AND pp.total > 0")->queryScalar(array(':tgl' => $tgl, ':sales' => $sales));
        $setor['piutang'] = (int)Yii::app()->db->createCommand("SELECT IFNULL(SUM(ppp.total),0) FROM {{pelunasan_piutang}} ppp
            WHERE ppp.tgl = :tgl AND ppp.salesman_id = :sales AND ppp.id_bank = :id_bank")
            ->queryScalar(array(':tgl' => $tgl, ':sales' => $sales, ':id_bank' => DEFKASPELPIU));
        $setor['retur_botol'] = (int)Yii::app()->db->createCommand("SELECT IFNULL(-SUM(pb.kas_keluar),0) FROM {{botol}} pb
            WHERE pb.tgl = :tgl AND pb.salesman_id = :sales")->queryScalar(array(':tgl' => $tgl, ':sales' => $sales));
        $setor['biaya_sales'] = (int)Yii::app()->db->createCommand("SELECT IFNULL(-SUM(pbs.total),0) FROM {{biaya_sales}} pbs
            WHERE pbs.tgl = :tgl AND pbs.salesman_id = :sales")->queryScalar(array(':tgl' => $tgl, ':sales' => $sales));
        $setor['retur_jual'] = (int)Yii::app()->db->createCommand("SELECT IFNULL(SUM(pp.uang_muka),0) FROM {{penjualan}} pp
            WHERE pp.tgl = :tgl AND pp.salesman_id = :sales AND pp.total < 0")->queryScalar(array(':tgl' => $tgl, ':sales' => $sales));
        $setor['total'] = array_sum($setor);
        return $setor;
    }
    static function get_report_pnjl($from, $to, $jnstrans, $internal,
                                    $supp_id, $sales_id, $pasar_id, $konsumen_id, $tgl = true)
    {
        if ($tgl) {
            $select [] = 'pp.tgl Tanggal';
            $group[] = 'pp.tgl';
        }
        $select [] = "pb.barang_name Nama Barang";
        $group[] = 'pb.barang_name';
        if ($sales_id !== null) {
            $select [] = 'ps.salesman_name Salesman';
            $group[] = 'ps.salesman_name';
        }
        if ($supp_id !== null) {
            $select [] = 'ps1.supplier_name Suplier';
            $group[] = 'ps1.supplier_name';
        }
        if ($konsumen_id !== null) {
            $select [] = 'pk.konsumen_name Konsumen';
            $group[] = 'pk.konsumen_name';
        }
        if ($pasar_id !== null) {
            $select [] = 'pp1.pasar_name Pasar';
            $group[] = 'pp1.pasar_name';
            $select [] = "pa.area_name Area";
            $group [] = "pa.area_name";
        }
        $select [] = "IF(pb.bonus,'B','-') Bonus";
        $group [] = "pb.bonus";
        $select [] = "FORMAT(SUM(pdp.pcs)/pb.max_2_pcs,2) 'Kuantitas Penjualan'";
        $select [] = "pb.sat_max Satuan";
        $group [] = "pb.sat_max";
        $select [] = "SUM(pdp.pcs) 'Kuantitas Jual (pieces)'";
        if ($internal) {
            $select [] = "SUM(pdp.bruto) 'Penjualan Bruto'";
            $select [] = "SUM(pdp.totalpot) Potongan";
            $select [] = "SUM(pdp.nominal) 'Penjualan Neto'";
        }
        $query = Yii::app()->db->createCommand();
        $query->select($select);
        $query->from = '{{penjualan}} pp';
        $query->join('{{detil_penjualan}} pdp', '( pp.penjualan_id = pdp.penjualan_id  )');
        $query->join('{{barang}} pb', '( pdp.barang_id = pb.barang_id  )  ');
        $query->join('{{supplier}} ps1', '( pb.supplier_id = ps1.supplier_id  )  ');
        $query->join('{{salesman}} ps', '( pp.salesman_id = ps.salesman_id  )');
        $query->join('{{konsumen}} pk', '( pp.konsumen_id = pk.konsumen_id  ) ');
        $query->leftJoin('{{pasar}} pp1', '( pk.pasar_id = pp1.pasar_id  )  ');
        $query->join('{{area}} pa', '( pk.area_id = pa.area_id  )  ');
        $query->andWhere('pp.tgl >= :from', array(':from' => $from));
        $query->andWhere('pp.tgl <= :to', array(':to' => $to));
        if ($sales_id != null) {
            $query->andWhere('pp.salesman_id = :sales_id', array(':sales_id' => $sales_id));
        }
        if ($supp_id != null) {
            $query->andWhere('pb.supplier_id = :supp_id', array(':supp_id' => $supp_id));
        }
        if ($konsumen_id != null) {
            $query->andWhere('pp.konsumen_id = :konsumen_id', array(':konsumen_id' => $konsumen_id));
        }
        if ($pasar_id != null) {
            $query->andWhere('pk.pasar_id = :pasar_id', array(':pasar_id' => $pasar_id));
        }
        if ($jnstrans != null) {
            $total = $jnstrans == 'P' ? "pp.total >= 0" : "pp.total < 0";
            $query->andWhere($total);
        }
        $query->group($group);
        return $query->queryAll(true);
    }
    static function get_report_detil_nota($from, $to, $jnstrans, $sales_id, $supp_id, $pasar_id, $konsumen_id)
    {
        $select [] = "CONCAT(pp.doc_ref,' ') `No Faktur`";
        if ($sales_id !== null) {
            $select [] = 'ps1.salesman_name Salesman';
//            $group[] = 'ps.salesman_name';
        }
        $select [] = "pp.tgl Tanggal";
        if ($konsumen_id !== null) {
            $select [] = 'pk.konsumen_name Nama Konsumen';
        }
        $select [] = "ptk.type_konsumen_name Tipe";
        if ($pasar_id !== null) {
            $select [] = 'pp1.pasar_name Pasar';
        }
        $select [] = "pa.area_name Area";
        $select [] = "CONCAT(pb.barcode,' ') Kode";
        $select [] = "pb.barang_name Nama Barang";
        if ($supp_id !== null) {
            $select [] = 'ps.supplier_name Suplier';
        }
        $select [] = "pdp.jml Kuantitas Penjualan";
        $select [] = "pdp.sat Satuan";
        $select [] = "pdp.pcs 'Kuantitas Jual (pieces)'";
        $select [] = "IF(pb.bonus,'B','-') Bonus";
        $select [] = "CONCAT(pdp.disc1,'%') `Disc 1`";
        $select [] = "CONCAT(pdp.disc2,'%') `Disc 2`";
        $select [] = "pdp.pot Potongan";
        $select [] = "pdp.nominal Nominal";
        $select [] = "pp.total";
        $query = Yii::app()->db->createCommand();
        $query->select($select);
        $query->from = '{{penjualan}} pp';
        $query->join('{{detil_penjualan}} pdp', '( pp.penjualan_id = pdp.penjualan_id  )');
        $query->join('{{barang}} pb', '( pdp.barang_id = pb.barang_id  )  ');
        $query->join('{{supplier}} ps', '( pb.supplier_id = ps.supplier_id  ) ');
        $query->join('{{salesman}} ps1', '( pp.salesman_id = ps1.salesman_id  )');
        $query->join('{{konsumen}} pk', '( pp.konsumen_id = pk.konsumen_id  ) ');
        $query->leftJoin('{{type_konsumen}} ptk', '( pk.type_konsumen_id = ptk.type_konsumen_id  )');
        $query->leftJoin('{{pasar}} pp1', '( pk.pasar_id = pp1.pasar_id  )  ');
        $query->join('{{area}} pa', '( pk.area_id = pa.area_id  )  ');
        $query->order("pp.doc_ref ASC");
        $query->andWhere('pp.tgl >= :from', array(':from' => $from));
        $query->andWhere('pp.tgl <= :to', array(':to' => $to));
        if ($sales_id != null) {
            $query->andWhere('pp.salesman_id = :sales_id', array(':sales_id' => $sales_id));
        }
        if ($supp_id != null) {
            $query->andWhere('pb.supplier_id = :supp_id', array(':supp_id' => $supp_id));
        }
        if ($konsumen_id != null) {
            $query->andWhere('pp.konsumen_id = :konsumen_id', array(':konsumen_id' => $konsumen_id));
        }
        if ($pasar_id != null) {
            $query->andWhere('pk.pasar_id = :pasar_id', array(':pasar_id' => $pasar_id));
        }
        if ($jnstrans != null) {
            $total = $jnstrans == 'P' ? "pp.total >= 0" : "pp.total < 0";
            $query->andWhere($total);
        }
        return $query->queryAll(true);
    }
    static function get_report_kas_masuk($tgl, $sales_id)
    {
        $command = Yii::app()->db->createCommand("SELECT a.doc_ref `NO FAKTUR`,	pk.konsumen_name KONSUMEN,
	        a.total NILAI, a.diterima `KAS DITERIMA TGL INI`, a.ket 'KETERANGAN'
            FROM ((
			SELECT pp.doc_ref, pp.total, pp.uang_muka diterima,	pp.konsumen_id,	'FAKTUR' AS ket
			FROM {{penjualan}} AS pp
			WHERE pp.uang_muka > 0 AND pp.total > 0	AND pp.salesman_id = :sales_id AND pp.tgl = :tgl)
		    UNION (SELECT pp.doc_ref, pp.total,	pppd.kas_diterima diterima, ppp.konsumen_id, 'PIUTANG' AS ket
				FROM {{pelunasan_piutang}} AS ppp
				INNER JOIN {{pelunasan_piutang_detil}} AS pppd ON pppd.pelunasan_piutang_id = ppp.pelunasan_piutang_id
				INNER JOIN {{penjualan}} AS pp ON pppd.penjualan_id = pp.penjualan_id
				WHERE pp.total > 0 AND pppd.kas_diterima > 0 AND ppp.salesman_id = :sales_id AND ppp.tgl = :tgl
			)) a INNER JOIN {{konsumen}} pk ON a.konsumen_id = pk.konsumen_id");
        return $command->queryAll(true, array(':sales_id' => $sales_id, ':tgl' => $tgl));
    }
    static function get_kelola_stok($tgl)
    {
        $select [] = "DISTINCT(trans_no) trans_no";
        $select [] = "tran_date";
        $select [] = "reference";
        $select [] = "memo_ note";
        $query = Yii::app()->db->createCommand();
        $query->select($select);
        $query->from = "{{stock_moves}} psm";
        $query->leftJoin("{{comments}} c", '(psm.type = c.type) AND (psm.trans_no = c.type_no) ');
        $query->andWhere('psm.type = :type AND psm.tran_date = :tgl', array(':type' => KELOLASTOK, ':tgl' => $tgl));
        return $query->queryAll(true);
    }
    static function get_selisih_stok($tgl)
    {
        $select [] = "DISTINCT(trans_no) trans_no";
        $select [] = "tran_date";
        $select [] = "reference";
        $select [] = "memo_ note";
        $query = Yii::app()->db->createCommand();
        $query->select($select);
        $query->from = "{{stock_moves}} psm";
        $query->leftJoin("{{comments}} c", '(psm.type = c.type) AND (psm.trans_no = c.type_no) ');
        $query->andWhere('psm.type = :type AND psm.tran_date = :tgl', array(':type' => SELISIHSTOK, ':tgl' => $tgl));
        return $query->queryAll(true);
    }
    static function get_report_laba_kotor($from, $to, $salesman_id)
    {
        return Yii::app()->db->createCommand("SELECT CONCAT(cr.barcode,' ') Kode, cr.barang_name `Nama Barang`,
        cr.b `Penjualan Bruto`, cr.p Potongan, cr.n `Penjualan Netto`,	cr.h HPP, cr.n - cr.h `Laba Kotor` FROM
    (SELECT pb.barcode, pb.barang_name, SUM(pdp.bruto) b, SUM(pdp.totalpot) p, SUM(pdp.nominal) n,SUM(pdp.hpp) h
    FROM {{detil_penjualan}} pdp
	INNER JOIN {{penjualan}} pp ON ( pdp.penjualan_id = pp.penjualan_id  )
	INNER JOIN {{barang}} pb ON ( pdp.barang_id = pb.barang_id  )
	WHERE pp.tgl >= :from AND pp.tgl <= :to AND pp.salesman_id = :sales_id
	GROUP BY pb.barcode, pb.barang_name) cr")->queryAll(true, array(':from' => $from, ':to' => $to,
            ':sales_id' => $salesman_id));
    }
    static function get_report_biaya_sales($from, $to, $salesman_id)
    {
        return Yii::app()->db->createCommand("SELECT pbs.tgl Tanggal, pbs.bbm BBM, pbs.makan Makan,
    pbs.parkir Parkir, IFNULL((SELECT SUM(pbsl.amount) FROM {{biaya_sales_lain}} pbsl
    WHERE pbsl.biaya_sales_id = pbs.biaya_sales_id),0) `Lain-lain`
    FROM {{biaya_sales}} pbs
    WHERE pbs.tgl >= :from AND pbs.tgl <= :to AND pbs.salesman_id = :sales_id
    ORDER BY pbs.tgl")
            ->queryAll(true, array(':from' => $from, ':to' => $to,
                ':sales_id' => $salesman_id));
    }
    static function get_report_botol_sales($from, $to, $salesman_id)
    {
        return Yii::app()->db->createCommand("SELECT pb.tgl Tanggal, pjb.jenis_botol_name `Jenis Botol`,
         pbl.total `Kas Keluar` FROM {{botol}} pb INNER JOIN {{botol_detil}} pbl
        ON (pbl.botol_id = pb.botol_id) INNER JOIN {{jenis_botol}} pjb
        ON (pbl.jenis_botol_id = pjb.jenis_botol_id)
        WHERE (pb.tgl >= :from AND pb.tgl <= :to) AND pb.salesman_id = :sales_id
        ORDER BY pb.tgl")
            ->queryAll(true, array(':from' => $from, ':to' => $to,
                ':sales_id' => $salesman_id));
    }
    static function get_dos_detil($dos_id)
    {
        return Yii::app()->db->createCommand("SELECT pb.barcode, pb.barang_name, pdd.jml, pdd.sat, pdd.pcs
        FROM {{dos_detil}} pdd
	INNER JOIN {{barang}} pb ON ( pdd.barang_id = pb.barang_id  )
        WHERE pdd.dos_id = :dos_id")->queryAll(true, array(':dos_id' => $dos_id));
    }
    static function get_botol_detil($tgl, $sales)
    {
        return Yii::app()->db->createCommand("SELECT pbd.jenis_botol_id, SUM(pbd.jml) jml_faktur,
        SUM(pbd.jml) jml_data_gudang, 0 jml_selisih, pjb.price
FROM {{botol}} pb
	INNER JOIN {{botol_detil}} pbd ON ( pb.botol_id = pbd.botol_id  )
	INNER JOIN {{jenis_botol}} pjb ON ( pbd.jenis_botol_id = pjb.jenis_botol_id )
WHERE pb.tgl = :tgl AND pb.salesman_id = :sales
GROUP BY pbd.jenis_botol_id, jml_selisih, pjb.price
")->queryAll(true, array(':tgl' => $tgl, ':sales' => $sales));
    }
    static function get_barang_cmp()
    {
        return Yii::app()->db->createCommand("SELECT pb.barang_id, pb.barcode, pb.barang_name, pb.status,
IFNULL((SELECT SUM(psm.qty) FROM {{stock_moves}} psm
	WHERE psm.barang_id = pb.barang_id),0) - IFNULL((SELECT SUM(pdp.pcs) FROM {{detil_penjualan}} pdp
	INNER JOIN {{penjualan}} pp ON ( pdp.penjualan_id = pp.penjualan_id  )
	WHERE pp.final = 0 AND pdp.barang_id = pb.barang_id),0) sisa
FROM {{barang}} pb")->queryAll();
    }
    static function report_pengiriman($tgl, $sales)
    {
        return Yii::app()->db->createCommand("SELECT pp.tgl `Tgl`,pp.doc_ref `No. Faktur`,
        CONCAT(pk.konsumen_name,'  ') `Nama Konsumen`,CONCAT(pa.area_name,pk.address) Alamat, pp.total Netto
    FROM {{penjualan}} pp INNER JOIN {{konsumen}} pk ON ( pp.konsumen_id = pk.konsumen_id  )
		INNER JOIN {{area}} pa ON ( pk.area_id = pa.area_id  )
		WHERE pp.tgl = :tgl AND pp.salesman_id = :sales")
            ->queryAll(true, array(':tgl' => $tgl, ':sales' => $sales));
    }
    // ------------------------------------------------ DOS ----------------------------------------------------------------
    static function get_piutang($konsumen)
    {
        return Yii::app()->db->createCommand("
        SELECT * FROM (SELECT pp.penjualan_id,  pp.tgl, pp.doc_ref no_faktur, pp.total nilai,
        pp.total - (IF(SUM(pppd.kas_diterima) IS NULL, 0, SUM(pppd.kas_diterima)) + pp.uang_muka) sisa
FROM {{penjualan}} pp
	LEFT OUTER JOIN {{pelunasan_piutang_detil}} pppd ON ( pp.penjualan_id = pppd.penjualan_id  )
WHERE `konsumen_id` = :konsumen AND NOT pp.lunas
GROUP BY pp.penjualan_id, pp.doc_ref, pp.tgl, pp.total) spp
WHERE spp.sisa != 0
")->queryAll(true, array(':konsumen' => $konsumen));
    }
    static function get_piutang_per_sales($sales, $tgl)
    {
        return Yii::app()->db->createCommand("
        SELECT * FROM (SELECT pp.`penjualan_id`,pp.tgl, pp.doc_ref no_faktur, pp.total, pk.konsumen_code, pk.konsumen_name,
(SELECT pp.total - (IF(SUM(pppd.kas_diterima) IS NULL, 0, SUM(pppd.kas_diterima)) + pp.uang_muka)
	FROM {{pelunasan_piutang_detil}} pppd
	INNER JOIN {{pelunasan_piutang}} ppp ON (pppd.pelunasan_piutang_id = ppp.pelunasan_piutang_id)
	WHERE pppd.penjualan_id = pp.`penjualan_id`
	AND ppp.tgl <= :tgl
 ) sisa
FROM {{penjualan}} pp
	INNER JOIN {{konsumen}} pk ON ( pp.konsumen_id = pk.konsumen_id  )
WHERE NOT pp.`lunas` AND pp.salesman_id = :salesman_id) spp
WHERE spp.sisa != 0")->queryAll(true, array(':salesman_id' => $sales, ':tgl' => $tgl));
    }
    static function get_report_piutang_all($tgl)
    {
        return Yii::app()->db->createCommand("
        SELECT spp.doc_ref `No Faktur`, spp.tgl `Tgl Faktur`,spp.tempo `Tgl Jatuh Tempo`,
        spp.konsumen_code `Kode Konsumen`, spp.konsumen_name `Nama Konsumen`, spp.total `Nilai Faktur`,
        spp.sisa `Sisa Tagihan` FROM (SELECT pp.doc_ref, pp.tgl,pp.tempo,pk.konsumen_code, pk.konsumen_name, pp.total,
(SELECT pp.total - (IF(SUM(pppd.kas_diterima) IS NULL, 0, SUM(pppd.kas_diterima)) + pp.uang_muka)
	FROM {{pelunasan_piutang_detil}} pppd
	INNER JOIN {{pelunasan_piutang}} ppp ON (pppd.pelunasan_piutang_id = ppp.pelunasan_piutang_id)
	WHERE pppd.penjualan_id = pp.`penjualan_id`
	AND ppp.tgl <= :tgl) sisa
FROM {{penjualan}} pp
	INNER JOIN {{konsumen}} pk ON ( pp.konsumen_id = pk.konsumen_id  )
WHERE NOT pp.`lunas`) spp
WHERE spp.sisa != 0")->queryAll(true, array(':tgl' => $tgl));
    }
    static function get_pelunasan($tgl, $sales, $konsumen)
    {
        return Yii::app()->db->createCommand("SELECT pp.penjualan_id,  ppp.tgl, pp.doc_ref as no_faktur,
         pppd.sisa,
        pp.total as nilai,pppd.kas_diterima
FROM {{pelunasan_piutang}} ppp
	INNER JOIN {{pelunasan_piutang_detil}} pppd ON ( ppp.pelunasan_piutang_id = pppd.pelunasan_piutang_id  )
		INNER JOIN {{penjualan pp}} ON ( pppd.penjualan_id = pp.penjualan_id  )
WHERE ppp.tgl = :tgl AND
	ppp.salesman_id = :sales AND
	ppp.konsumen_id = :konsumen
GROUP BY pp.penjualan_id, no_faktur, nilai,pppd.kas_diterima")
            ->queryAll(true, array(':tgl' => $tgl, ':sales' => $sales, ':konsumen' => $konsumen));
    }
    static function get_retur_jual()
    {
        return Yii::app()->db->createCommand("SELECT pp.penjualan_id, pp.salesman_id, pp.konsumen_id, pp.doc_ref,
        pp.tgl, pp.tempo, -pp.sub_total sub_total, -pp.total total, -pp.uang_muka uang_muka,
        pp.no_bg_cek, -pp.sisa_tagihan sisa_tagihan, pp.lunas,
        pp.final, pp.id_user,pp.parent, -pp.bruto bruto
        FROM {{penjualan}} pp
        WHERE pp.total <= 0")
            ->queryAll();
    }
    static function get_retur_jual_detil($id)
    {
        return Yii::app()->db->createCommand("SELECT pdp.detil_penjualan, pdp.penjualan_id, pdp.barang_id, pdp.sat,
        -pdp.jml jml, pdp.price, pdp.disc1, pdp.disc2, pdp.pot, -pdp.nominal nominal, -pdp.pcs pcs
        FROM {{detil_penjualan}} pdp
        WHERE pdp.penjualan_id = :penjualan_id")
            ->queryAll(true, array(':penjualan_id' => $id));
    }
    static function get_dos_jual($tgl, $sales)
    {
        $command = Yii::app()->db->createCommand("
    SELECT IFNULL(a.barang_id,b.barang_id) barang_id,IFNULL(a.sat,b.sat) sat, IFNULL(a.jml,0) jml_dos,
	IFNULL(a.pcs,0) pcs_dos, IFNULL(b.pcs,0) pcs_dof, IFNULL(b.jml,0) jml_dof,
	(IFNULL(a.jml,0)-IFNULL(b.jml,0)) jml_sisa,(IFNULL(a.pcs,0)-IFNULL(b.pcs,0)) pcs_sisa,0 jml_riil,0 pcs_riil,
	((IFNULL(a.jml,0)-IFNULL(b.jml,0)) - 0) jml_selisih,((IFNULL(a.pcs,0)-IFNULL(b.pcs,0)) - 0) pcs_selisih
	FROM (SELECT pdd.barang_id, 'PCS' sat, SUM(pdd.pcs) jml, SUM(pdd.pcs) pcs
		FROM {{dos_detil}} pdd
	INNER JOIN {{dos}} pd ON ( pdd.dos_id = pd.dos_id  )
	WHERE pd.`tgl` = :tgl AND pd.`salesman_id` = :sales
	GROUP BY pdd.barang_id) AS a
    LEFT OUTER JOIN (SELECT pdp.barang_id, 'PCS' sat, SUM(pdp.pcs) jml, SUM(pdp.pcs) AS pcs
    FROM {{detil_penjualan}} pdp
	INNER JOIN {{penjualan}} pp ON ( pdp.penjualan_id = pp.penjualan_id  )
	WHERE pp.`tgl` = :tgl AND pp.`salesman_id` = :sales AND pp.total > 0
	GROUP BY pdp.barang_id) b ON (b.barang_id = a.barang_id AND a.sat = b.sat)
    UNION
    SELECT IFNULL(a.barang_id,b.barang_id) barang_id,IFNULL(a.sat,b.sat) sat, IFNULL(a.jml,0) jml_dos,
	IFNULL(a.pcs,0) pcs_dos, IFNULL(b.pcs,0) pcs_dof, IFNULL(b.jml,0) jml_dof,
	(IFNULL(a.jml,0)-IFNULL(b.jml,0)) jml_sisa,(IFNULL(a.pcs,0)-IFNULL(b.pcs,0)) pcs_sisa,0 jml_riil,0 pcs_riil,
	((IFNULL(a.jml,0)-IFNULL(b.jml,0)) - 0) jml_selisih,((IFNULL(a.pcs,0)-IFNULL(b.pcs,0)) - 0) pcs_selisih
	FROM (SELECT pdd.barang_id, 'PCS' sat, SUM(pdd.pcs) jml, SUM(pdd.pcs) pcs
    FROM {{dos_detil}} pdd
	INNER JOIN {{dos}} pd ON ( pdd.dos_id = pd.dos_id  )
	WHERE pd.`tgl` = :tgl AND pd.`salesman_id` = :sales
	GROUP BY pdd.barang_id) AS a
    RIGHT OUTER JOIN (SELECT pdp.barang_id, 'PCS' sat,SUM(pdp.pcs) jml, SUM(pdp.pcs) AS pcs
    FROM {{detil_penjualan}} pdp
	INNER JOIN {{penjualan}} pp ON ( pdp.penjualan_id = pp.penjualan_id  )
	WHERE pp.`tgl` = :tgl AND pp.`salesman_id` = :sales AND pp.total > 0
	GROUP BY pdp.barang_id) b ON (b.barang_id = a.barang_id AND a.sat = b.sat)");
        return $command->queryAll(true, array(':tgl' => $tgl, ':sales' => $sales));
    }
    static function print_dof_gudang($id)
    {
        $command = Yii::app()->db->createCommand("
        SELECT pb.`barcode` `Kode Barang`,pb.`barang_name` `Nama Barang`,
        ' ' `Jumlah Barang Baik`,' ' `Jumlah Barang Rusak`
        FROM {{dof_detil}} pdd INNER JOIN {{barang}} pb
        ON (pdd.`barang_id` = pb.`barang_id`)
        WHERE pdd.`dof_id` = :dof_id");
        return $command->queryAll(true, array(':dof_id' => $id));
    }
    // ------------------------------------------------ Void ----------------------------------------------------------------
    static function get_voided($type)
    {
        $void = Yii::app()->db->createCommand()->select('id')->from('mt_voided')->where('type=:type',
            array(
                ':type' => $type
            ))->queryColumn();
        return $void;
    }
    static function get_max_type_no($type)
    {
        $type_no = app()->db->createCommand()->select("MAX(type_no)")
            ->from("{{gl_trans}}")->where('type=:type', array(':type' => $type))->queryScalar();
        return $type_no == false ? 0 : $type_no;
    }
    static function get_max_type_no_stock($type)
    {
        $type_no = app()->db->createCommand()->select("MAX(trans_no)")
            ->from("{{stock_moves}}")->where('type=:type', array(':type' => $type))->queryScalar();
        return $type_no == false ? 0 : $type_no;
    }
    // --------------------------------------------- Bank Trans -------------------------------------------------------------
    static function get_next_trans_no_bank_trans($type)
    {
//        $db = BankTrans::model()->getDbConnection();
        $total = app()->db->createCommand(
            "SELECT MAX(trans_no)
            FROM psn_bank_trans where type = :type")
            ->queryScalar(array(':type' => $type));
        if ($total === false) {
            $total = 0;
        } else {
            $total++;
        }
        return $total;
    }
    static function get_next_trans_saldo_awal()
    {
        $db = GlTrans::model()->getDbConnection();
        $total = $db->createCommand(
            "SELECT MAX(type_no) FROM {{gl_trans}} where type=:type")
            ->queryScalar(array(':type' => SALDO_AWAL));
        return $total == null ? 0 : $total + 1;
    }
    static function get_next_trans($type)
    {
//        $db = GlTrans::model()->getDbConnection();
        $total = app()->db->createCommand(
            "SELECT MAX(type_no) FROM {{gl_trans}} where type=:type")
            ->queryScalar(array(':type' => $type));
        if ($total === false) {
            $total = 0;
        } else {
            $total++;
        }
        return $total;
    }
    static function get_ledger_trans($from, $to)
    {
        $rows = Yii::app()->db->createCommand(
            "SELECT
            mt_gl_trans.tran_date,
            mt_gl_trans.type,
            mt_gl_trans.type_no,
            refs.reference,
            SUM(IF(mt_gl_trans.amount>0, mt_gl_trans.amount,0)) as amount,
            users.user_id
            FROM
            mt_gl_trans
            LEFT JOIN mt_refs as refs ON
            (mt_gl_trans.type=refs.type AND mt_gl_trans.type_no=refs.type_no),users
            WHERE mt_gl_trans.tran_date BETWEEN '$from' AND '$to'
            GROUP BY mt_gl_trans.tran_date,mt_gl_trans.type,
            mt_gl_trans.type_no,mt_gl_trans.users_id
            ")->queryAll();
        return $rows;
    }
    static function get_general_ledger_trans($from, $to)
    {
        $rows = Yii::app()->db->createCommand(
            "SELECT
            mt_gl_trans.type,
            mt_gl_trans.type_no,
            mt_gl_trans.tran_date,
            CONCAT(mt_chart_master.account_code,' ',mt_chart_master.account_name) as account,
            mt_gl_trans.amount
            FROM
            mt_gl_trans
            INNER JOIN mt_chart_master ON mt_gl_trans.account = mt_chart_master.account_code
            WHERE mt_gl_trans.tran_date BETWEEN '$from' AND '$to'
            ")->queryAll();
        return $rows;
    }
    static function get_bank_trans_view()
    {
        global $systypes_array;
        $bfw = U::get_balance_before_for_bank_account($_POST['trans_date_mulai'],
            $_POST['bank_act']);
        $arr['data'][] = array(
            'type' => 'Saldo Awal - ' . sql2date($_POST['trans_date_mulai']),
            'ref' => '',
            'tgl' => '',
            'debit' => $bfw >= 0 ? number_format($bfw, 2) : '',
            'kredit' => $bfw < 0 ? number_format($bfw, 2) : '',
            'neraca' => '',
            'person' => ''
        );
        $credit = $debit = 0;
        $running_total = $bfw;
        if ($bfw > 0) $debit += $bfw;
        else $credit += $bfw;
        $result = U::get_bank_trans_for_bank_account($_POST['bank_act'],
            $_POST['trans_date_mulai'], $_POST['trans_date_sampai']);
        foreach ($result as $myrow) {
            $running_total += $myrow->amount;
            $jemaat = get_jemaat_from_user_id($myrow->users_id);
            $arr['data'][] = array(
                'type' => $systypes_array[$myrow->type],
                'ref' => $myrow->ref,
                'tgl' => sql2date($myrow->trans_date),
                'debit' => $myrow->amount >= 0 ? number_format($myrow->amount, 2)
                    : '',
                'kredit' => $myrow->amount < 0 ? number_format(-$myrow->amount,
                    2) : '',
                'neraca' => number_format($running_total, 2),
                'person' => $jemaat->real_name
            );
            if ($myrow->amount > 0) $debit += $myrow->amount;
            else $credit += $myrow->amount;
        }
        $arr['data'][] = array(
            'type' => 'Saldo Akhir - ' . sql2date($_POST['trans_date_sampai']),
            'ref' => '',
            'tgl' => '',
            'debit' => $running_total >= 0 ? number_format($running_total, 2) : '',
            'kredit' => $running_total < 0 ? number_format(-$running_total, 2) : '',
            'neraca' => '', // number_format($debit + $credit, 2),
            'person' => ''
        );
        return $arr;
    }
    static function get_balance_before_for_bank_account($from, $bank_account = null)
    {
//        $db = BankTrans::model()->getDbConnection();
        $query = app()->db->createCommand();
        $query->select = "SUM(amount)";
        $query->from = "{{bank_trans}}";
        $query->where('trans_date < :from', array(':from' => $from));
        if ($bank_account != null) {
            $query->andWhere('id_bank = :id_bank', array(':id_bank' => $bank_account));
        }
        $total = $query->queryScalar();
        return $total ? $total : 0;
    }
    static function get_bank_trans_for_bank_account($bank_account, $from, $to)
    {
        $criteria = new CDbCriteria();
        if ($bank_account != null)
            $criteria->addCondition("bank_act =" . $bank_account);
        $criteria->addBetweenCondition("trans_date", $from, $to);
        $criteria->order = "trans_date, id";
        return BankTrans::model()->findAll($criteria);
    }
    static function get_prefs($name)
    {
        $criteria = new CDbCriteria();
        if ($name != null) $criteria->addCondition("name ='$name'");
        else return null;
        $prefs = SysPrefs::model()->find($criteria);
        return $prefs->value;
    }
    static function get_act_code_from_bank_act($bank_act)
    {
        $bank = Bank::model()->findByPk($bank_act);
        if ($bank != null) return $bank->accountCode->account_code;
        else return false;
    }
    static function get_sql_for_journal_inquiry($from, $to)
    {
        $rows = Yii::app()->db->createCommand()->select(
            "gl_trans.tran_date,gl_trans.type,refs.reference,Sum(IF(amount>0, amount,0)) AS amount,
    comments.memo_,gl_trans.person_id,gl_trans.type_no")->from('gl_trans')->join(
            'comments',
            'gl_trans.type = comments.type AND gl_trans.type_no = comments.type_no')->Join(
            'refs',
            'gl_trans.type = refs.type AND gl_trans.type_no = refs.type_no')->where(
            "gl_trans.amount!=0 and gl_trans.tran_date >= '$from'
		        AND gl_trans.tran_date <= '$to'")->group('gl_trans.type, gl_trans.type_no')->order(
            'tran_date desc')->queryAll();
        return $rows;
    }
    static function add_gl($type, $trans_id, $date_, $ref, $account, $memo_, $comment_,
                           $amount)
    {
        if ($amount == 0) return;
        $person_id = Yii::app()->user->getId();
        $is_bank_to = self::is_bank_account($account);
        self::add_gl_trans($type, $trans_id, $date_, $account, $memo_, $amount,
            $person_id);
        if ($is_bank_to) {
            self::add_bank_trans($type, $trans_id, $is_bank_to, $ref, $date_,
                $amount, $person_id);
        }
        self::add_comments($type, $trans_id, $date_, $comment_);
        // return $trans_id;
    }
    // --------------------------------------------- Gl Trans ---------------------------------------------------------------
    static function is_bank_account($account_code)
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("account_code = :account_code");
        $criteria->params = array(":account_code" => $account_code);
        $bank_act = Bank::model()->find($criteria);
        if ($bank_act != null) return $bank_act->id_bank;
        else return false;
    }
    static function add_gl_trans($type, $trans_id, $date_, $account, $memo_,
                                 $amount, $person_id)
    {
        $gl_trans = new GlTrans();
        $gl_trans->type = $type;
        $gl_trans->type_no = $trans_id;
        $gl_trans->tran_date = $date_;
        $gl_trans->account_code = $account;
        $gl_trans->memo_ = $memo_;
        $gl_trans->id_user = $person_id;
        $gl_trans->amount = $amount;
        if (!$gl_trans->save())
            throw new Exception("Gagal menyimpan jurnal." . CHtml::errorSummary($gl_trans));
    }
    static function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_,
                                   $amount, $person_id)
    {
        $bank_trans = new BankTrans();
        $bank_trans->type = $type;
        $bank_trans->trans_no = $trans_no;
        $bank_trans->id_bank = $bank_act;
        $bank_trans->ref = $ref;
        $bank_trans->trans_date = $date_;
        $bank_trans->amount = $amount;
        $bank_trans->id_user = $person_id;
        if (!$bank_trans->save())
            throw new Exception("Gagal menyimpan transaksi bank." . CHtml::errorSummary($bank_trans));
    }
    static function add_comments($type, $type_no, $date_, $memo_)
    {
        if ($memo_ != null && $memo_ != "") {
            $comment = new Comments();
            $comment->type = $type;
            $comment->type_no = $type_no;
            $comment->date_ = $date_;
            $comment->memo_ = $memo_;
            if (!$comment->save())
                throw new Exception("Gagal menyimpan keterangan." . CHtml::errorSummary($comment));
        }
    }
    static function add_stock_moves($type, $trans_no, $tran_date, $barang_id, $gudang_id, $qty,
                                    $reference, $price, $discount_percent = 0, $visible = 1)
    {
        $move = new StockMoves;
        $move->type = $type;
        $move->trans_no = $trans_no;
        $move->tran_date = $tran_date;
        $move->price = $price;
        $move->reference = $reference;
        $move->qty = $qty;
        $move->discount_percent = $discount_percent;
        $move->visible = $visible;
        $move->barang_id = $barang_id;
        $move->gudang_id = $gudang_id;
        if (!$move->save())
            throw new Exception("Gagal menyimpan jurnal. " . CHtml::errorSummary($move));
    }
    // --------------------------------------------- Comments ---------------------------------------------------------------
    static function get_comments($type, $type_no)
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("type=" . $type);
        $criteria->addCondition("id=" . $type_no);
        return Comments::model()->find($criteria);
    }
    static function update_comments($type, $id, $date_, $memo_)
    {
        if ($date_ == null) {
            U::delete_comments($type, $id);
            U::add_comments($type, $id,
                Yii::app()->dateFormatter->format('yyyy-MM-dd', time()),
                $memo_);
        } else {
            $criteria = new CDbCriteria();
            $criteria->addCondition("type=" . $type);
            $criteria->addCondition("id=" . $id);
            $criteria->addCondition("date_=" . $date_);
            $comment = Comments::model()->find($criteria);
            $comment->memo_ = $memo_;
            $comment->save();
        }
    }
    static function delete_comments($type, $type_no)
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("type=" . $type);
        $criteria->addCondition("id=" . $type_no);
        $comment = Comments::model()->find($criteria);
        $comment->delete();
    }
    // ---------------------------------------------- Report ----------------------------------------------------------------
    static function get_beban()
    {
        $rows = app()->db->createCommand("SELECT account_code FROM mt_chart_master WHERE account_code REGEXP '^5[1-9]'")->queryAll();
        return $rows;
    }
    static function get_daftar_master_konsumen($code, $nama, $phone, $hp, $hp2, $tempo, $status)
    {
        $query = app()->db->createCommand();
        $query->select("CONCAT(pk.konsumen_code,' ') `Kode Konsumen`, pk.konsumen_name `Nama Konsumen`,
        pk.phone Phone, pk.hp HP, pk.hp2 `HP 2`, pk.tempo Tempo, pk.address Alamat, pk.status `Status`");
        $query->from("{{konsumen}} pk");
        if ($code !== "") {
            $query->andWhere("konsumen_code like :konsumen_code", array(":konsumen_code" => $code . "%"));
        }
        if ($nama !== "") {
            $query->andWhere("konsumen_name like :konsumen_name", array(":konsumen_name" => "%" . $nama . "%"));
        }
        if ($phone !== "") {
            $query->andWhere("phone like :phone", array(":phone" => "%" . $phone . "%"));
        }
        if ($hp !== "") {
            $query->andWhere("hp like :hp", array(":hp" => "%" . $hp . "%"));
        }
        if ($hp2 !== "") {
            $query->andWhere("hp2 like :hp2", array(":hp2" => "%" . $hp2 . "%"));
        }
        if ($tempo !== "") {
            $query->andWhere("tempo like :tempo", array(":tempo" => "%" . $tempo . "%"));
        }
        if ($status !== "") {
            $query->andWhere("status like :status", array(":status" => "%" . $status . "%"));
        }
        return $query->queryAll(true);
    }
    static function get_mutasi_kas_ditangan($start_date, $end_date)
    {
        $criteria = new CDbCriteria();
        $criteria->addBetweenCondition('trans_date', $start_date, $end_date);
        $model = BankTrans::model()->findAll($criteria);
        return $model;
    }
    static function get_arr_kode_rekening_pengeluaran($code = "")
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("account_type='" . Prefs::TypeCostAct() . "'");
        if ($code != "account_code" && $code != "")
            $criteria->addCondition("account_code='$code'");
        $model = ChartMaster::model()->findAll($criteria);
        $daftar = array();
        foreach ($model as $coderek) {
            $daftar[$coderek['account_code']] = $coderek['account_name'];
        }
        return $daftar;
    }
    static function get_pengeluaran_detil_kode_rekening($start_date, $end_date,
                                                        $code)
    {
        $rows = Yii::app()->db->createCommand()->select(
            "a.tran_date,a.memo_,IF(a.amount > 0,a.amount,'') as debit,IF(a.amount < 0,-a.amount,'') as kredit")->from(
            "mt_gl_trans a")->rightJoin("mt_chart_master b",
            "a.account=b.account_code
    AND a.tran_date between :start and :end",
            array(
                ':start' => $start_date,
                ':end' => $end_date
            ))->leftJoin('mt_voided c', "a.type_no=c.id AND c.type=a.type")->where(
            "b.account_code=:code and a.type != :type and ISNULL(c.date_)",
            array(
                'code' => $code,
                'type' => VOID
            ))->order("a.tran_date")->queryAll();
        // ->where("b.account_code=:code",array('code'=>$code))
        return $rows;
    }
    static function get_pengeluaran_per_kode_rekening($start_date, $end_date)
    {
        $rows = Yii::app()->db->createCommand()->select(
            "b.account_code,b.account_name as nama_rekening,IFNULL(sum(a.amount),0) as total_beban")->from(
            "mt_gl_trans a")->rightJoin("mt_chart_master b",
            "a.account=b.account_code
    AND a.tran_date between :start and :end",
            array(
                ':start' => $start_date,
                ':end' => $end_date
            ))->where("b.account_type=:type and !b.inactive",
            array(
                ':type' => Prefs::TypeCostAct()
            ))->group("b.account_name")->order("b.account_code")->queryAll();
        return $rows;
    }
    static function get_total_pengeluaran($start_date, $end_date, $code = "")
    {
        $kode = $code == "" ? "" : "and b.account_code = '$code'";
        $rows = Yii::app()->db->createCommand()->select("sum(a.amount) as total_beban")->from(
            "mt_gl_trans a")->join("mt_chart_master b",
            "a.account=b.account_code")->where(
            "a.tran_date between :start and :end and b.account_type=:type $kode",
            array(
                ':start' => $start_date,
                ':end' => $end_date,
                ':type' => Prefs::TypeCostAct()
            ))->queryScalar();
        return $rows == null ? 0 : $rows;
    }
    static function get_detil_pendapatan($start_date, $end_date)
    {
        $rows = Yii::app()->db->createCommand()->select(
            "b.account_name as nama_rekening,IFNULL(-sum(a.amount),0) as total_pendapatan")->from(
            "mt_gl_trans a")->rightJoin("mt_chart_master b",
            "a.account=b.account_code and
        a.tran_date between :start and :end",
            array(
                ':start' => $start_date,
                ':end' => $end_date
            ))->where("b.account_type=:type and !b.inactive",
            array(
                ':type' => Prefs::TypePendapatanAct()
            ))->group("b.account_name")->order("b.account_code")->queryAll();
        return $rows;
    }
    static function get_total_pendapatan($start_date, $end_date)
    {
        $rows = Yii::app()->db->createCommand()->select("-sum(a.amount) as total_pendapatan")->from(
            "mt_gl_trans a")->join("mt_chart_master b",
            "a.account=b.account_code")->where(
            "a.tran_date between :start and :end and b.account_type=:type",
            array(
                ':start' => $start_date,
                ':end' => $end_date,
                ':type' => Prefs::TypePendapatanAct()
            ))->order("b.account_code")->queryScalar();
        return $rows == null ? 0 : $rows;
    }
    static function get_chart_master_beban()
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("account_type = " . Prefs::TypeCostAct());
        return ChartMaster::model()->findAll($criteria);
    }
    static function account_in_gl_trans($account)
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("account_code = '$account'");
        $count = GlTrans::model()->count($criteria);
        return $count > 0;
    }
    static function account_used_bank($account)
    {
        $criteria = new CDbCriteria();
        $criteria->addCondition("account_code = '$account'");
        $count = Bank::model()->count($criteria);
        return $count > 0;
    }
    static function report_nota_debet($from, $to, $supplier_id, $salesman_id)
    {
        $supplier = "";
        $param = array(':from' => $from, ':to' => $to);
        if ($supplier_id != null) {
            $supplier = "AND pnd.supplier_id = :supplier_id";
            $param[':supplier_id'] = $supplier_id;
        }
        $salesman = "";
        if ($salesman_id != null) {
            $salesman = "AND pnd.salesman_id = :salesman_id";
            $param[':salesman_id'] = $salesman_id;
        }
        $comm = Yii::app()->db->createCommand("
        SELECT pnd.tgl,pk.konsumen_name,pndd.lembar,-pndd.nilai nilai,-pndd.amount amount
        FROM psn_nota_debet AS pnd
        INNER JOIN psn_nota_debet_detil AS pndd ON pndd.nota_debet_id = pnd.nota_debet_id
        INNER JOIN psn_konsumen AS pk ON pndd.konsumen_id = pk.konsumen_id
        WHERE pnd.tgl >= :from AND pnd.tgl <= :to $supplier $salesman");
        return $comm->queryAll(true, $param);
    }
    static function report_sedang_pesan()
    {
        $comm = Yii::app()->db->createCommand("
        SELECT ps.supplier_name,pp.tgl,pp.no_po,pb.barcode,pb.barang_name,
        Sum(psm.qty) qty,Sum(ppd.jml) jml
        FROM psn_po_detil AS ppd
        INNER JOIN psn_po AS pp ON ppd.po_id = pp.po_id AND pp.done = 0
        INNER JOIN psn_barang AS pb ON ppd.barang_id = pb.barang_id
        INNER JOIN psn_supplier AS ps ON pp.supplier_id = ps.supplier_id
        LEFT OUTER JOIN psn_stock_moves AS psm ON ppd.barang_id = psm.barang_id
        GROUP BY ps.supplier_name,pp.no_po,pp.tgl,pb.barcode,pb.barang_name");
        return $comm->queryAll(true);
    }
    static function report_setoran_kas($from, $to, $salesman_id)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT psks.doc_ref,DATE_FORMAT(psks.tgl,'%d/%m/%y') tgl,psks.faktur,
        psks.pelunasan_piutang,psks.retur_botol,
        psks.biaya_sales,psks.retur_penjualan_tunai,psks.kas_disetor
        FROM psn_setor_kas_sales AS psks
        WHERE psks.tgl >= :from AND psks.tgl <= :to AND psks.salesman_id = :salesman_id");
        return $comm->queryAll(true, array(':from' => $from,
            ':to' => $to, 'salesman_id' => $salesman_id));
    }
    static function report_barang_habis()
    {
        $comm = Yii::app()->db->createCommand("
        SELECT ps.supplier_name,pb.barcode,pb.barang_name,Sum(psm.qty) saldo, pb.stok_minimal
        FROM psn_barang AS pb
        INNER JOIN psn_supplier AS ps ON pb.supplier_id = ps.supplier_id
        LEFT OUTER JOIN psn_stock_moves AS psm ON psm.barang_id = pb.barang_id
        GROUP BY ps.supplier_name,pb.barcode,pb.barang_name,pb.stok_minimal
        HAVING Sum(psm.qty) <= pb.stok_minimal");
        return $comm->queryAll(true);
    }
    static function report_utang()
    {
        $comm = Yii::app()->db->createCommand("
        SELECT pp.no_faktur_beli no_faktur,pp.tgl,ps.supplier_code,ps.supplier_name,pp.total nilai,
        pp.sisa_tagihan - (IF (SUM(ppud.kas_dibayar) IS NULL,0,SUM(ppud.kas_dibayar))) AS sisa,pp.tgl_tempo
        FROM psn_pembelian AS pp
        LEFT  JOIN psn_pelunasan_utang_detil AS ppud ON  ppud.trans_mode = 0 AND pp.pembelian_id = ppud.mode_id
        LEFT JOIN psn_terima AS pt ON  pt.terima_id = pp.ref_id
        LEFT JOIN psn_po AS ppo ON pt.po_id = ppo.po_id
        LEFT JOIN psn_supplier AS ps ON ppo.supplier_id = ps.supplier_id
        WHERE pp.total >= 0 AND NOT pp.lunas #AND ppo.supplier_id = 1
        GROUP BY pp.pembelian_id,pp.no_faktur_beli,pp.tgl,pp.total
        HAVING sisa != 0
        ORDER BY ps.supplier_name");
        return $comm->queryAll(true);
    }
    static function report_pembelian($from, $to, $supplier_id)
    {
        $supplier = "";
        $param = array(':from' => $from, ':to' => $to);
        if ($supplier_id != null) {
            $supplier = "AND ppo.supplier_id = :supplier_id";
            $param[':supplier_id'] = $supplier_id;
        }
        $comm = Yii::app()->db->createCommand("
        SELECT pp.tgl,pp.no_faktur_beli,psn_barang.barcode,psn_barang.barang_name,
        ppd.jml,ppd.price,ppd.disc1,ppd.disc2,ppd.pot,ppd.nominal
        FROM psn_pembelian AS pp
        INNER JOIN psn_pembelian_detil AS ppd ON ppd.pembelian_id = pp.pembelian_id
        INNER JOIN psn_barang ON ppd.barang_id = psn_barang.barang_id
        INNER JOIN psn_terima AS pt ON pp.ref_id = pt.terima_id
        INNER JOIN psn_po AS ppo ON pt.po_id = ppo.po_id
        WHERE pp.total >= 0 AND pp.tgl >= :from AND pp.tgl <= :to $supplier");
        return $comm->queryAll(true, $param);
    }
    static function report_mutasi_bank($from, $to, $bank_id)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT	pbt.trans_date,	pbt.ref, pbt.type,
        if(pbt.type = 1 or pbt.type = 0,SUBSTRING(pk.note,1,50),'') `Keterangan`,
	    0 awal,	IF( pbt.amount >= 0 , pbt.amount , 0 ) AS `in`,
	    IF( pbt.amount < 0 , - pbt.amount , 0 ) AS `out`,
	    0 akhir, pbt.amount
        FROM psn_kas pk
	    right outer JOIN psn_bank_trans pbt
	    ON (pbt.type = 0 or pbt.type = 1) AND pk.kas_id = pbt.trans_no
        WHERE pbt.id_bank = :bank_id AND pbt.trans_date >= :from
        AND pbt.trans_date <= :to");
        return $comm->queryAll(true, array(':from' => $from, ':to' => $to, ':bank_id' => $bank_id));
    }
    static function cetak_master_barang()
    {
        $comm = Yii::app()->db->createCommand("
        SELECT	pb.barcode,	pb.barang_name,	if(pb.bonus,'B','') bonus,	pb.price_pcs,	pb.price_lsn,
	    pb.price_pak,	pb.pak_2_pcs,	pb.price_max,	pb.sat_max,	pb.max_2_pcs,	pb.stok_minimal,
	    if(pb.status,'Aktif','Non Aktif') status,	pb.price_buy,	pb.ppn,	ps.supplier_code,	ps.supplier_name
        FROM psn_supplier ps
	    INNER JOIN psn_barang pb ON ps.supplier_id = pb.supplier_id");
        return $comm->queryAll(true);
    }
    static function report_jurnal_umum($from, $to)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT pgt.type_no,	pgt.tran_date,
	    pgt.memo_,IF(pgt.amount>=0,pgt.amount,'') AS Debet,
        IF(pgt.amount<0,-pgt.amount,'') AS Kredit,
	    pcm.account_name, pcm.account_code
        FROM psn_chart_master pcm
	        INNER JOIN psn_gl_trans pgt
	        ON pcm.account_code = pgt.account_code
        WHERE pgt.type = 1 AND pgt.tran_date >= :from
            AND pgt.tran_date <= :to");
        return $comm->queryAll(true, array(':from' => $from, ':to' => $to));
    }
    static function report_pengelolaan_persediaan($from, $to)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT psm.tran_date,psm.reference,pb.barcode,
        pb.barang_name,psm.qty,pc.memo_
        FROM psn_stock_moves AS psm
        INNER JOIN psn_barang AS pb ON psm.barang_id = pb.barang_id
        INNER JOIN psn_comments AS pc ON psm.type = pc.type AND psm.trans_no = pc.type_no
        WHERE psm.type = :type AND psm.tran_date >= :from
            AND psm.tran_date <= :to");
        return $comm->queryAll(true, array(':type' => KELOLASTOK, ':from' => $from, ':to' => $to));
    }
    static function report_selisih_persediaan($from, $to)
    {
        $comm = Yii::app()->db->createCommand('
        SELECT psm.tran_date,psm.reference,pb.barcode,
        pb.barang_name,psm.qty,pc.memo_
        FROM psn_stock_moves AS psm
        INNER JOIN psn_barang AS pb ON psm.barang_id = pb.barang_id
        INNER JOIN psn_comments AS pc ON psm.type = pc.type AND psm.trans_no = pc.type_no
        WHERE psm.type = :type AND psm.tran_date >= :from
            AND psm.tran_date <= :to');
        return $comm->queryAll(true, array(':type' => SELISIHSTOK, ':from' => $from, ':to' => $to));
    }
    static function report_lr_pendapatan($from, $to)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT pcm.account_code,pcm.account_name,IFNULL(-Sum(pgt.amount),0) total FROM psn_gl_trans AS pgt
	    RIGHT JOIN psn_chart_master pcm ON (pgt.account_code = pcm.account_code AND
            pgt.tran_date >= :from AND pgt.tran_date <= :to)
        WHERE pcm.kategori = :kategori
        GROUP BY pcm.account_code");
        return $comm->queryAll(true, array(':from' => $from, ':to' => $to,
            ':kategori' => KATEGORIPENDAPATAN));
    }
    static function get_general_ledger($account_code, $from, $to)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT DATE_FORMAT(ngt.tran_date,'%c/%e/%Y') AS tgl,psn_trans_tipe.tipe_name,
        IF(ngt.amount > 0, ngt.amount, '') AS Debit,
        IF(ngt.amount < 0, -ngt.amount, '') AS Credit,
        0 AS Balance,ngt.amount,psn_refs.reference,
        psn_trans_tipe.tipe_name
        FROM psn_gl_trans AS ngt
        INNER JOIN psn_refs ON ngt.type = psn_refs.type AND ngt.type_no = psn_refs.type_no
        INNER JOIN psn_trans_tipe ON ngt.type = psn_trans_tipe.tipe_id
        WHERE ngt.account_code = :account_code AND
        ngt.tran_date >= :from AND ngt.tran_date <= :to");
        return $comm->queryAll(true, array(':account_code' => $account_code,
            ':from' => $from, ':to' => $to));
    }
    static function get_gl_before($account_code, $date)
    {
        $comm = Yii::app()->db->createCommand("
        SELECT IFNULL(SUM(ngt.amount),0) FROM psn_gl_trans ngt
        WHERE ngt.account_code = :account_code AND ngt.tran_date < :date");
        return $comm->queryScalar(array(":account_code" => $account_code, ":date" => $date));
    }
    static function report_kartu_stok($barang_id, $from, $to, $gudang_id = GUDANG_PST)
    {
        $comm = Yii::app()->db->createCommand("SELECT DATE_FORMAT(nsm.tran_date,'%c/%e/%Y') tgl,nsm.reference doc_ref,
            0 `before`,IF(nsm.qty >0,nsm.qty,0) `in`,IF(nsm.qty <0,-nsm.qty,0) `out`,
            0 `after`,nsm.qty,psn_trans_tipe.tipe_name
            FROM psn_stock_moves AS nsm
            INNER JOIN psn_barang nb ON nsm.barang_id = nb.barang_id
            INNER JOIN psn_trans_tipe ON nsm.type = psn_trans_tipe.tipe_id
            WHERE DATE(nsm.tran_date) >= :from AND DATE(nsm.tran_date) <= :to
            AND nb.barang_id = :barang_id AND nsm.gudang_id = :gudang_id
            ORDER BY nsm.tran_date");
        return $comm->queryAll(true, array(':barang_id' => $barang_id,
            ':from' => $from, ':to' => $to, ':gudang_id' => $gudang_id));
    }
}